<?php
class PerformanceData extends HTech_Db_Table
{
    protected $_name    = 'view_performance_data';
    protected $_primary = 'PerformanceDataId'; 
    
    public function __construct($subject, $config = array()) {
        parent::__construct($config);
        $this->_subject = $subject;
    }

    protected function _setGridMetadata()
    {
        $this->_gridMetadata['title'] = $this->translate("Performance data");
        $this->_gridMetadata['titleMods'] = array('Subject','Program','ProductLine','LocationShortName','Username','Metric');
        $this->_gridMetadata['params'] = array('Subject','ProgramId','LineId','LocationId','OfficerUserId','Metric');
        $this->_gridMetadata['headers'] = array
            (
                array('PerformanceDataId',   50, $this->translate("Id"),           'Number'),
                array('Subject',            100, $this->translate("Subject"),      'String'),
                array('ShortName',          150, $this->translate("Name"),         'String'),
                array('Metric',             200, $this->translate("Metric"),       'String'),
                array('Value',              200, $this->translate("Value"),        'Number',   'Standard'),
                array('A',                  100, $this->translate("Mark A"),       'Number',   'Standard'),
                array('B',                  100, $this->translate("Mark B"),       'Number',   'Standard'),
                array('C',                  200, $this->translate("Mark C"),       'Number',   'Standard'),
                array('Score',               80, $this->translate("Score"),        'Number',   'Standard'),
                array('Color',              180, $this->translate("Color"),        'String'),
                array('Date',                80, $this->translate("Date"),         'Date',     'Date')
            );
        return $this;
    }
    
    public function p2date($mode)
    {
        switch($mode) {
            case 'Location':
                return $this->_location();
            case 'Officer':
                return $this->_officer();
            case 'Program':
                return $this->_program();
            case 'Line':
                return $this->_line();
            default:
                return;
        }
    }
    
    public function chartData($id,$metric = array(),$options = array())
    {
        switch($this->_subject) {
            case 'Location':
                return $this->_getChartData('LocationId',$id,$metric,$options);
            case 'Officer':
                return $this->_getChartData('OfficerUserId',$id,$metric,$options);
            case 'Program':
                return $this->_getChartData('ProgramId',$id,$metric,$options);
            case 'Line':
                return $this->_getChartData('LineId',$id,$metric,$options);
            default:
                return;
        }
    }
    
    public function chartMeta()
    {
        $max = Zend_Registry::get('db')
            ->query("select sum(greatest(ScoreAA,ScoreAB,ScoreBC,ScoreCC)) max from performance_metrics where Subject = ? and Metric != 'Score'", $this->_subject)
            ->fetchAll(); 
        
        return array('Score' => array('max' => $max[0]->max, 'step' => $max[0]->max/5)); 
    }
    
    private function _location() 
    {
        $date = date('Y-m-d');
        $indicators = array
        (
            'Clients'            => array('label' => $this->translate('Clients'), 'decimals' => 0),
            'FemalePercentage'   => array('label' => $this->translate('Female pct.'), 'decimals' => 2),
            'BalancePrincipal'   => array('label' => $this->translate('Active portfolio'), 'decimals' => 2),
            'Par15'              => array('label' => $this->translate('Par15'), 'decimals' => 2),
            'WriteOffPercentage' => array('label' => $this->translate('Write-off pct.'), 'decimals' => 2),
            'Interest'           => array('label' => $this->translate('Interest'), 'decimals' => 2),
            'Disbursements'      => array('label' => $this->translate('Disbursements'), 'decimals' => 0),
            'DisbursedAmount'    => array('label' => $this->translate('Disbursed amount'), 'decimals' => 2),
            'Meetings'           => array('label' => $this->translate('Meetings'), 'decimals' => 0),
            'ClientAssistance'   => array('label' => $this->translate('Client assist.'), 'decimals' => 0),
            'PersonalGrowth'     => array('label' => $this->translate('Personal growth'), 'decimals' => 0),
            'ChurchMinistry'     => array('label' => $this->translate('Church ministry'), 'decimals' => 0),
            'Training'           => array('label' => $this->translate('Training'), 'decimals' => 0),
            'Sustainability'     => array('label' => $this->translate('Sustainability'), 'decimals' => 2),
            'Score'              => array('label' => $this->translate('Score'), 'decimals' => 2)
        );
        $select = $this->select();
        $select->where(sprintf("Date = '%s'", $date));
        $select->where("Subject = 'Location'");
        $data = $this->fetchAll($select)->toArray();
        $pd = array();
        foreach($data as $row) {
            foreach($row as $col => $val) {
                $pd[$row['ShortName']][$row['Metric']][$col] = $val;
            }
        }
        return array('indicators' => $indicators, 'data' => $pd);       
    }
    
    private function _officer() 
    {
        $date = date('Y-m-d');
        $indicators = array
        (
            'Clients'            => array('label' => $this->translate('Clients'), 'decimals' => 0),
            'FemalePercentage'   => array('label' => $this->translate('Female pct.'), 'decimals' => 2),
            'BalancePrincipal'   => array('label' => $this->translate('Active portfolio'), 'decimals' => 2),
            'Par15'              => array('label' => $this->translate('Par15'), 'decimals' => 2),
            'WriteOffPercentage' => array('label' => $this->translate('Write-off pct.'), 'decimals' => 2),
            'Interest'           => array('label' => $this->translate('Interest'), 'decimals' => 2),
            'Disbursements'      => array('label' => $this->translate('Disbursements'), 'decimals' => 0),
            'DisbursedAmount'    => array('label' => $this->translate('Disbursed amount'), 'decimals' => 2),
            'Meetings'           => array('label' => $this->translate('Meetings'), 'decimals' => 0),
            'ClientAssistance'   => array('label' => $this->translate('Client assist.'), 'decimals' => 0),
            'PersonalGrowth'     => array('label' => $this->translate('Personal growth'), 'decimals' => 0),
            'ChurchMinistry'     => array('label' => $this->translate('Church ministry'), 'decimals' => 0),
            'Training'           => array('label' => $this->translate('Training'), 'decimals' => 0),
            'Score'              => array('label' => $this->translate('Score'), 'decimals' => 2)
        );
        $select = $this->select();
        $select->where(sprintf("Date = '%s'", $date));
        $select->where("Subject = 'Officer'");
        $data = $this->fetchAll($select)->toArray();
        $pd = array();
        foreach($data as $row) {
            foreach($row as $col => $val) {
                $pd[$row['OfficerLocationFullName']][$row['ShortName']][$row['Metric']][$col] = $val;
            }
        }
        return array('indicators' => $indicators, 'data' => $pd);       
    }
    
    private function _program() 
    {
        $date = date('Y-m-d');
        $indicators = array
        (
            'Clients'            => array('label' => $this->translate('Clients'), 'decimals' => 0),
            'FemalePercentage'   => array('label' => $this->translate('Female pct.'), 'decimals' => 2),
            'BalancePrincipal'   => array('label' => $this->translate('Active portfolio'), 'decimals' => 2),
            'Par15'              => array('label' => $this->translate('Par15'), 'decimals' => 2),
            'WriteOffPercentage' => array('label' => $this->translate('Write-off pct.'), 'decimals' => 2),
            'Interest'           => array('label' => $this->translate('Interest'), 'decimals' => 2),
            'Disbursements'      => array('label' => $this->translate('Disbursements'), 'decimals' => 0),
            'DisbursedAmount'    => array('label' => $this->translate('Disbursed amount'), 'decimals' => 2),
            'Score'              => array('label' => $this->translate('Score'), 'decimals' => 2)
        );
        $select = $this->select();
        $select->where(sprintf("Date = '%s'", $date));
        $select->where("Subject = 'Program'");
        $data = $this->fetchAll($select)->toArray();
        $pd = array();
        foreach($data as $row) {
            foreach($row as $col => $val) {
                $pd[$row['ProgramProductLine']][$row['ShortName']][$row['Metric']][$col] = $val;
            }
        }
        return array('indicators' => $indicators, 'data' => $pd);       
    }    
    
    private function _line() 
    {
        $date = date('Y-m-d');
        $indicators = array
        (
            'Clients'            => array('label' => $this->translate('Clients'), 'decimals' => 0),
            'FemalePercentage'   => array('label' => $this->translate('Female pct.'), 'decimals' => 2),
            'BalancePrincipal'   => array('label' => $this->translate('Active portfolio'), 'decimals' => 2),
            'Par15'              => array('label' => $this->translate('Par15'), 'decimals' => 2),
            'WriteOffPercentage' => array('label' => $this->translate('Write-off pct.'), 'decimals' => 2),
            'Interest'           => array('label' => $this->translate('Interest'), 'decimals' => 2),
            'Disbursements'      => array('label' => $this->translate('Disbursements'), 'decimals' => 0),
            'DisbursedAmount'    => array('label' => $this->translate('Disbursed amount'), 'decimals' => 2),
            'Score'              => array('label' => $this->translate('Score'), 'decimals' => 2)
        );
        $select = $this->select();
        $select->where(sprintf("Date = '%s'", $date));
        $select->where("Subject = 'Line'");
        $data = $this->fetchAll($select)->toArray();
        $pd = array();
        foreach($data as $row) {
            foreach($row as $col => $val) {
                $pd[$row['ShortName']][$row['Metric']][$col] = $val;
            }
        }
        return array('indicators' => $indicators, 'data' => $pd);       
    }
    
    
    private function _getChartData($subject, $subjectId, $metric, $options = array())
    {
        $type = isset($options['type']) ? $options['type'] : 'barStack';
        $mode = isset($options['mode']) ? $options['mode'] : 'Day';
        $zdate = new Zend_Date();
        $pfd = new Zend_Db_Table(array('name' => 'performance_data', 'primary' => 'PerformanceDataId'));
        $select = $pfd->select()->where($subject . ' = ?', $subjectId);
        switch($mode) {
            case 'Day': 
                $select->where('Date > date_sub(curdate(), INTERVAL 7 DAY)');
                $zdateFormat = 'D d';
                break;
            case 'Week': 
                $select->where('Date > date_sub(curdate(), INTERVAL 4 WEEK)');
                $select->where('DAYOFWEEK(Date) = 6');
                $zdateFormat = 'W';
                break;
            case 'Month': 
                $select->where('Date >  date_sub(curdate(), INTERVAL 12 MONTH)');
                $select->where('day(date_add(Date, INTERVAL 1 DAY)) = 1');
                $zdateFormat = 'M';
                break;
            default:
                $zdateFormat = 'D';            
        }
        $select->order('PerformanceDataId');
        $areaStack = array();
        $barStack = array();
        
        $c = 0; 
        foreach($metric as $m) {
            $c++;
            $select->where('Metric = ?', $m);
            $pf = $pfd->fetchAll($select);
            foreach($pf as $p) {
                $zdate->set($p->Date, 'Y-m-d');
                $areaStack[$p->Metric][$zdate->toString($zdateFormat)] = doubleval($p->Score);
                $barStack[$zdate->toString($zdateFormat)][$p->Metric] = doubleval($p->Score);
            }
        }
        
        if($c == 0) {
            $pf = $pfd->fetchAll($select);
            foreach($pf as $p) {
                $zdate->set($p->Date, 'Y-m-d');
                $areaStack[$p->Metric][$zdate->toString($zdateFormat)] = doubleval($p->Score);
                $barStack[$zdate->toString($zdateFormat)][$p->Metric] = doubleval($p->Score);
            }
        }
        
        switch($type) {
            case 'barStack': return $barStack;
            case 'areaStack': return $areaStack;
        }
        return $barStack;
    }
      
}